using System;
using System.Collections.Generic;
using System.Text;
using Pixysoft.Framework.XSchEmA.Entity;

namespace Pixysoft.Framework.XSchEmA.Sql
{
    abstract internal class ASqlBuilder : ISqlBuilder
    {
        public virtual string DropDatabase(string databasename)
        {
            return string.Format("DROP DATABASE {0}", databasename);
        }

        public virtual string CreateDatabase(string databasename)
        {
            return string.Format("CREATE DATABASE {0}", databasename);
        }

        public virtual string CreateTable(DatabaseSchema database, TableSchema table)
        {
            if (table == null)
                return null;

            StringBuilder builder = new StringBuilder();
            builder.Append("CREATE TABLE " + table.TableName + "\r\n");
            builder.Append("(");
            foreach (ColumnSchema column in table.Columns)
            {
                builder.Append("\r\n");
                builder.Append(" " + column.ColumnName + "\t");
                builder.Append(GetDataType(column.DataType));
                builder.Append(CreateDefaultValue(column));
                builder.Append(CreateNullable(column));
                builder.Append(",");
            }
            builder.Remove(builder.Length - 1, 1);
            builder.Append("\r\n");
            builder.Append(")");


            //builder.Append(";");
            return builder.ToString();
        }
        protected string CreateNullable(ColumnSchema column)
        {
            if (column.IsNullable)
                return "";
            else
                return "\tNOT NULL";
        }
        protected string CreateDefaultValue(ColumnSchema column)
        {
            if (column.DefaultValue == null || column.DefaultValue.Trim() == "")
                return "";
            else
                return "\tDEFAULT " + column.DefaultValue;
        }

        public string DropTable(TableSchema table)
        {
            if (table == null)
                return null;

            return DropTable(table.TableName);
        }
        public string DropTable(string tablename)
        {
            if (tablename == null)
                return null;
            //return "DROP TABLE " + table.TableName + ";";
            return "DROP TABLE " + tablename;
        }

        public string AlterTableCreateColumn(ColumnSchema column)
        {
            if (column == null)
                return null;
            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + column.TableName + " ADD \r\n");
            builder.Append(" " + column.ColumnName + "\t");
            builder.Append(GetDataType(column.DataType));
            builder.Append(CreateDefaultValue(column));
            builder.Append(CreateNullable(column));
            //builder.Append(";");
            return builder.ToString();
        }
        public string AlterTableDropColumn(ColumnSchema column)
        {
            if (column == null)
                return null;

            return AlterTableDropColumn(column.TableName, column.ColumnName);
        }
        public string AlterTableDropColumn(string tablename, string columnname)
        {
            if (tablename == null)
                return null;

            if (columnname == null)
                return null;

            //return "ALTER TABLE " + column.TableName + " DROP COLUMN " + column.ColumnName + ";";
            return "ALTER TABLE " + tablename + " DROP COLUMN " + columnname;
        }

        abstract public string AlterTableModifyColumn(ColumnSchema column);
        abstract public string AlterTableModifyColumnNullable(ColumnSchema column);
        protected string CreateStrongNullable(ColumnSchema column)
        {
            if (column.IsNullable)
                return "\tNULL";
            else
                return "\tNOT NULL";
        }

        public string AlterTableCreateConstraint(PrimaryKeySchema pk)
        {
            if (pk == null)
                return null;

            if (pk.ColumnNames.Count == 0)
                return null;


            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + pk.TableName + " ADD \r\n");
            builder.Append(" CONSTRAINT " + pk.ConsName + "\r\n");
            builder.Append(" PRIMARY KEY (");

            builder.Append(SchemaUtility.ListToString<string>(pk.ColumnNames));

            //builder.Append(");");
            builder.Append(")");

            return builder.ToString();
        }
        public virtual string AlterTableDropConstraint(PrimaryKeySchema pk)
        {
            if (pk == null)
                return null;


            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + pk.TableName + " DROP ");
            //builder.Append(" CONSTRAINT " + pk.PkName + ";");
            builder.Append(" CONSTRAINT " + pk.ConsName);
            return builder.ToString();
        }

        public string AlterTableCreateConstraint(ForeignKeySchema fk)
        {
            if (fk == null)
                return null;
            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + fk.FkTableName + " ADD \r\n");
            builder.Append(" CONSTRAINT " + fk.ConsName + "\r\n");
            builder.Append(" FOREIGN KEY (" + SchemaUtility.ListToString<string>(fk.FkColumnNames) + ")\r\n");
            builder.Append(" REFERENCES " + fk.PkTableName + "(" + SchemaUtility.ListToString<string>(fk.PkColumnNames) + ")");
            return builder.ToString();

        }
        public string AlterTableDropConstraint(ForeignKeySchema fk)
        {
            if (fk == null)
                return null;
            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + fk.FkTableName + " DROP ");
            //builder.Append(" CONSTRAINT " + fk.FkName + ";");
            builder.Append(" CONSTRAINT " + fk.ConsName);
            return builder.ToString();
        }

        public string AlterTableCreateConstraint(UniqueSchema unique)
        {
            if (unique == null)
                return null;

            StringBuilder builder = new StringBuilder();
            //builder.Append("ALTER TABLE " + unique.TableName + " ADD CONSTRAINT " + unique.IndexName + " UNIQUE(" + unique.ColumnName + ");");
            builder.Append("ALTER TABLE " + unique.TableName + " ADD \r\n");
            builder.Append("CONSTRAINT " + unique.ConsName + " UNIQUE(");

            builder.Append(SchemaUtility.ListToString<string>(unique.ColumnNames));

            builder.Append(")");

            return builder.ToString();

        }
        public string AlterTableDropConstraint(UniqueSchema unique)
        {
            if (unique == null)
                return null;

            StringBuilder builder = new StringBuilder();
            builder.Append("ALTER TABLE " + unique.TableName + " DROP ");
            //builder.Append(" CONSTRAINT " + unique.IndexName + ";");
            builder.Append(" CONSTRAINT " + unique.ConsName);
            return builder.ToString();

        }

        public virtual string AlterTableCreateIndex(IndexSchema index)
        {
            if (index == null)
                return null;

            //CREATE UNIQUE INDEX {0} ON {1}({2})

            StringBuilder builder = new StringBuilder();

            builder.Append(string.Format("CREATE INDEX {0} ON {1}({2})",
                index.ConsName,
                index.TableName,
                SchemaUtility.ListToString<string>(index.ColumnNames)));

            return builder.ToString();

        }
        abstract public string AlterTableDropIndex(IndexSchema index);

        public string GetDataType(DataTypeSchema datatype)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(datatype.DataType);

            if (datatype.Length > 0)
            {
                builder.Append("(" + datatype.Length.ToString() + ")");
                return builder.ToString();
            }

            if (datatype.Precision > 0)
            {
                builder.Append("(" + datatype.Precision.ToString());

                if (datatype.Scale >= 0)
                    builder.Append("," + datatype.Scale.ToString());

                builder.Append(")");

                return builder.ToString();
            }

            return builder.ToString();
        }

        private void test()
        {
            ColumnSchema col = new ColumnSchema("CST_DATATIMESTAMP", "ORGDATASTATUS", new DataTypeSchema("INT", -1, -1, -1));

            Console.Write(AlterTableCreateColumn(col));
        }
    }
}
